package a_o2odata_deal.utils.zhibo_zf

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/2/10 13:56
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object update_zf_zhubo_address20 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    //利用机构表为主播添加地址
    val jigou_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/1/zhibojigou_v1")
    jigou_data.createOrReplaceTempView("jigou_data")


    sqlContext.read.json(a_aa_amainpackage.a_o2odata_deal.config.config.ali_zb_path).selectExpr("anchorId","affiliateId")
      .dropDuplicates("anchorId")
      .createOrReplaceTempView("ali_zb")

    sqlContext.sql(
      s"""
         |select
         |t1.anchorId,
         |t2.company_name,
         |t2.province,
         |t2.city,
         |t2.district,
         |t2.regional_ID
         |from ali_zb t1
         |inner join
         |jigou_data t2
         |on t1.affiliateId=t2.affiliateId
       """.stripMargin).createOrReplaceTempView("mid")

    val source_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/1/zhubo_v3/")
    source_data.createOrReplaceTempView("source_data")
    println("处理前条数:"+source_data.count())
    val result = sqlContext.sql(
      s"""
         |select
         |t1.*,
         |case when t2.anchorId is null then t1.company_name else t2.company_name end as company_names,
         |case when t2.anchorId is null then t1.province else t2.province end as provinces,
         |case when t2.anchorId is null then t1.city else t2.city end as citys,
         |case when t2.anchorId is null then t1.district else t2.district end as districts,
         |case when t2.anchorId is null then t1.regional_ID else t2.regional_ID end as regional_IDs
         |from source_data t1
         |left join
         |mid t2
         |on t1.anchorId=t2.anchorId
         """.stripMargin).drop("company_name","province","city","district","regional_ID")
      .withColumnRenamed("company_names","company_name")
      .withColumnRenamed("provinces","province")
      .withColumnRenamed("citys","city")
      .withColumnRenamed("districts","district")
      .withColumnRenamed("regional_IDs","regional_ID")

    println("处理后条数:"+result.count())
    result.where("is_selfLive='false' and regional_ID!='-1'").selectExpr("anchorId","company_name",
      "province","city","district","regional_ID","institution_name").show(false)

    result.printSchema()

    println(result.where("company_name!='-1' and company_name is not null ").count())
    result.repartition(4).write.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/1/zhubo_v4/")


  }

}
